--作业题
--1、找出全部夺得3连贯的队伍
select team,count(*) as wincount 
from (select team,(year-row_number() over (partition by team order by year asc)) as gid from t1) t2
group by team,gid
having wincount>=3;
--2、找出每个id在在一天之内所有的波峰与波谷值
select * 
from (select id,time,price,case when price<lagprice and price<leadprice then '波谷'
                          when price>lagprice and price>leadprice then '波峰'
                     else null end as feature
      from (select id, time, price,
                   nvl(lag(price) over(partition by id order by time),0) lagprice,
                   nvl(lead(price) over(partition by id order by time),0) leadprice
            from t2
      )t3
)t4
where feature is not null;
--3、写SQL
--3.1、每个id浏览时长、步长
select id,(unix_timestamp(max(dt),'yyyy/MM/dd HH:mm')-unix_timestamp(min(dt),'yyyy/MM/dd HH:mm'))/60 as time,count(browseid)
from t3
group by id;
--3.2、如果两次浏览之间的间隔超过30分钟，认为是两个不同的浏览时间；再求每个id浏览时长、步长
select id,(unix_timestamp(max(dt),'yyyy/MM/dd HH:mm')-unix_timestamp(min(dt),'yyyy/MM/dd HH:mm'))/60 as time,count(id)
from (select id,dt,mark,sum(mark)over(partition by id order by dt ) as diff_visit
      from (select id,dt,case when (unix_timestamp(dt,'yyyy/MM/dd HH:mm')-unix_timestamp(lag(dt) over(partition by id order by dt asc),'yyyy/MM/dd HH:mm'))/60>30 then 1
                else 0 end as mark 
            from t3
      )t4
)t5 
group by id,diff_visit;